using System;
using System.Text;
using TccAg.VO;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace TccAg.DA
{
    public class ParametroDA
    {
        private String queryInsert = 
            @"INSERT INTO parametros( 
                                     PERACAD, 
                                     TAMPOPULACAO, 
                                     TXCRUZAMENTO, 
                                     TXMUTACAO, 
                                     NUMITERACOES, 
                                     MAXAPTIDAO
                                    ) 
                             VALUES( 
                                    @peracad, 
                                    @tampopulacao, 
                                    @txcruzamento, 
                                    @txmutacao, 
                                    @numiteracoes, 
                                    @maxaptidao)";
        private String queryUpdate = 
            @"UPDATE parametros SET 
                peracad = @peracad, 
                tampopulacao = @tampopulacao,   
                txcruzamento = @txcruzamento, 
                txmutacao = @txmutacao, 
                numiteracoes = @numiteracoes, 
                maxaptidao = @maxaptidao 
             WHERE 
                ID = @id";
        private String queryDelete = "DELETE FROM parametros WHERE ID = @id";
        private String queryLoadAll = 
            @"SELECT  
                id, 
                peracad, 
                tampopulacao, 
                txcruzamento, 
                txmutacao, 
                numiteracoes, 
                maxaptidao 
              FROM 
                parametros";
        private String queryLoadByPk = 
            @"SELECT  
                id, 
                peracad, 
                tampopulacao, 
                txcruzamento, 
                txmutacao, 
                numiteracoes, 
                maxaptidao 
              FROM 
                parametros 
              WHERE 
                ID = @id";
        private String queryLoadByFk = 
            @"SELECT  
                id, 
                peracad, 
                tampopulacao, 
                txcruzamento, 
                txmutacao, 
                numiteracoes, 
                maxaptidao 
              FROM 
                parametros 
              WHERE 
                PERACAD = @peracad";
        private SqlConnection conexao = new SqlConnection("Pegar conexão");

        //INSERIR 
        public int inserir(ParametroVO parametro)
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryInsert;

                if (parametro.Peracad == 0)
                        cmd.Parameters.Add(new SqlParameter("@peracad", (object) parametro.Peracad));
                else
                        cmd.Parameters.Add(new SqlParameter("@peracad", (object) DBNull.Value));

                if (parametro.Tampopulacao == 0)
                        cmd.Parameters.Add(new SqlParameter("@tampopulacao", (object) parametro.Tampopulacao));
                else
                        cmd.Parameters.Add(new SqlParameter("@tampopulacao", (object) DBNull.Value));

                if (parametro.Txcruzamento == 0)
                        cmd.Parameters.Add(new SqlParameter("@txcruzamento", (object) parametro.Txcruzamento));
                else
                        cmd.Parameters.Add(new SqlParameter("@txcruzamento", (object) DBNull.Value));

                if (parametro.Txmutacao == 0)
                        cmd.Parameters.Add(new SqlParameter("@txmutacao", (object) parametro.Txmutacao));
                else
                        cmd.Parameters.Add(new SqlParameter("@txmutacao", (object) DBNull.Value));

                if (parametro.Numiteracoes == 0)
                        cmd.Parameters.Add(new SqlParameter("@numiteracoes", (object) parametro.Numiteracoes));
                else
                        cmd.Parameters.Add(new SqlParameter("@numiteracoes", (object) DBNull.Value));

                if (parametro.Maxaptidao == 0)
                        cmd.Parameters.Add(new SqlParameter("@maxaptidao", (object) parametro.Maxaptidao));
                else
                        cmd.Parameters.Add(new SqlParameter("@maxaptidao", (object) DBNull.Value));

                conexao.Open();
                return cmd.ExecuteNonQuery();

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //ATUALIZAR 
        public int atualizar(ParametroVO parametro)
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                if (parametro.Id == 0)
                    cmd.Parameters.Add(new SqlParameter("@id", (object)parametro.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryUpdate;

                if (parametro.Peracad == 0)
                        cmd.Parameters.Add(new SqlParameter("@peracad", (object) parametro.Peracad));
                else
                        cmd.Parameters.Add(new SqlParameter("@peracad", (object) DBNull.Value));
                if (parametro.Tampopulacao == 0)
                        cmd.Parameters.Add(new SqlParameter("@tampopulacao", (object) parametro.Tampopulacao));
                else
                        cmd.Parameters.Add(new SqlParameter("@tampopulacao", (object) DBNull.Value));
                if (parametro.Txcruzamento == 0)
                        cmd.Parameters.Add(new SqlParameter("@txcruzamento", (object) parametro.Txcruzamento));
                else
                        cmd.Parameters.Add(new SqlParameter("@txcruzamento", (object) DBNull.Value));
                if (parametro.Txmutacao == 0)
                        cmd.Parameters.Add(new SqlParameter("@txmutacao", (object) parametro.Txmutacao));
                else
                        cmd.Parameters.Add(new SqlParameter("@txmutacao", (object) DBNull.Value));
                if (parametro.Numiteracoes == 0)
                        cmd.Parameters.Add(new SqlParameter("@numiteracoes", (object) parametro.Numiteracoes));
                else
                        cmd.Parameters.Add(new SqlParameter("@numiteracoes", (object) DBNull.Value));
                if (parametro.Maxaptidao == 0)
                        cmd.Parameters.Add(new SqlParameter("@maxaptidao", (object) parametro.Maxaptidao));
                else
                        cmd.Parameters.Add(new SqlParameter("@maxaptidao", (object) DBNull.Value));
                conexao.Open();
                return cmd.ExecuteNonQuery();

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //EXCLUIR 
        public int excluir(ParametroVO parametro)
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryDelete;

                if (parametro.Id == 0)
                    cmd.Parameters.Add(new SqlParameter("@id", (object)parametro.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                conexao.Open();
                return cmd.ExecuteNonQuery();

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //LOAD ALL 
        public List<ParametroVO> loadAll()
        {
            try
            {
                List<ParametroVO> objRetorno = new List<ParametroVO>();
                ParametroVO objTemp;
                SqlCommand cmd;
                SqlDataReader dr;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadAll;

                conexao.Open();
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        objTemp = new ParametroVO();
                        if (dr["id"] != DBNull.Value)
                           objTemp.Id = (int)dr["id"];

                        if (dr["peracad"] != DBNull.Value)
                           objTemp.Peracad = (int)dr["peracad"];

                        if (dr["tampopulacao"] != DBNull.Value)
                           objTemp.Tampopulacao = (int)dr["tampopulacao"];

                        if (dr["txcruzamento"] != DBNull.Value)
                           objTemp.Txcruzamento = (int)dr["txcruzamento"];

                        if (dr["txmutacao"] != DBNull.Value)
                           objTemp.Txmutacao = (int)dr["txmutacao"];

                        if (dr["numiteracoes"] != DBNull.Value)
                           objTemp.Numiteracoes = (int)dr["numiteracoes"];

                        if (dr["maxaptidao"] != DBNull.Value)
                           objTemp.Maxaptidao = (int)dr["maxaptidao"];

                        objRetorno.Add(objTemp);
                    }
                }
                return objRetorno;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //LOAD BY PK - Passa um objeto do tipo VO para em casos onde usar chave primária composta ter essas propriedades preenchidas
        public ParametroVO loadByPk(ParametroVO parametro)
        {
            try
            {
                ParametroVO objRetorno = null;
                ParametroVO objTemp;
                SqlCommand cmd;
                SqlDataReader dr;
                cmd = conexao.CreateCommand();
                if (parametro.Id == 0)
                    cmd.Parameters.Add(new SqlParameter("@id", (object)parametro.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadByPk;

                conexao.Open();
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    dr.Read();
                    {
                        objTemp = new ParametroVO();
                        if (dr["id"] != DBNull.Value)
                           objTemp.Id = (int)dr["id"];

                        if (dr["peracad"] != DBNull.Value)
                           objTemp.Peracad = (int)dr["peracad"];

                        if (dr["tampopulacao"] != DBNull.Value)
                           objTemp.Tampopulacao = (int)dr["tampopulacao"];

                        if (dr["txcruzamento"] != DBNull.Value)
                           objTemp.Txcruzamento = (int)dr["txcruzamento"];

                        if (dr["txmutacao"] != DBNull.Value)
                           objTemp.Txmutacao = (int)dr["txmutacao"];

                        if (dr["numiteracoes"] != DBNull.Value)
                           objTemp.Numiteracoes = (int)dr["numiteracoes"];

                        if (dr["maxaptidao"] != DBNull.Value)
                           objTemp.Maxaptidao = (int)dr["maxaptidao"];

                        objRetorno = objTemp;
                    }
                }
                return objRetorno;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //LOAD BY FK - Passa um objeto do tipo VO para em casos onde usar chave estrangeira composta ter essas propriedades preenchidas
        public List<ParametroVO> loadByFk(ParametroVO parametro)
        {
            try
            {
                List<ParametroVO> objRetorno = new List<ParametroVO>();
                ParametroVO objTemp;
                SqlCommand cmd;
                SqlDataReader dr;
                cmd = conexao.CreateCommand();
                if (parametro.Peracad != 0)
                    cmd.Parameters.Add(new SqlParameter("@peracad", (object)parametro.Peracad));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadByFk;

                conexao.Open();
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    dr.Read();
                    {
                        objTemp = new ParametroVO();
                        if (dr["id"] != DBNull.Value)
                           objTemp.Id = (int)dr["id"];

                        if (dr["peracad"] != DBNull.Value)
                           objTemp.Peracad = (int)dr["peracad"];

                        if (dr["tampopulacao"] != DBNull.Value)
                           objTemp.Tampopulacao = (int)dr["tampopulacao"];

                        if (dr["txcruzamento"] != DBNull.Value)
                           objTemp.Txcruzamento = (int)dr["txcruzamento"];

                        if (dr["txmutacao"] != DBNull.Value)
                           objTemp.Txmutacao = (int)dr["txmutacao"];

                        if (dr["numiteracoes"] != DBNull.Value)
                           objTemp.Numiteracoes = (int)dr["numiteracoes"];

                        if (dr["maxaptidao"] != DBNull.Value)
                           objTemp.Maxaptidao = (int)dr["maxaptidao"];

                        objRetorno.Add(objTemp);
                    }
                }
                return objRetorno;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //DATATABLE LOAD ALL 
        public DataTable dataTableLoadAll()
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadAll;

                conexao.Open();
                SqlDataAdapter dtAdpt = new SqlDataAdapter(cmd);
                DataTable dtTbl = new DataTable("tab_parametros");

                dtAdpt.Fill(dtTbl);
                return dtTbl;

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

    }
}
